package org.kjtc.exportService;

import java.io.FileOutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import javax.servlet.ServletOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.kjtc.balanceEntity.ChargingStation;
import org.kjtc.balanceEntity.ReviewRecord;
import org.kjtc.util.DoubleUtil;

@SuppressWarnings({ "deprecation", "unused" })
public class ExportStageSummary<T> {

	public void exportExcel(String title, String[] headers, List<ReviewRecord> list, ServletOutputStream servletOutputStream) throws Exception { 
		// 声明一个工作薄  
        HSSFWorkbook workbook = new HSSFWorkbook();  
        // 生成一个表格  
        HSSFSheet sheet = workbook.createSheet(title);
        // 纸张大小、方向
        HSSFPrintSetup ps = sheet.getPrintSetup();    
        ps.setLandscape(false); // 打印方向，true：横向，false：纵向(默认)     
        ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); //纸张类型 
        
        HSSFFooter footer = sheet.getFooter();
        footer.setCenter(HSSFFooter.page() + "/ " + HSSFFooter.numPages() + "页");
        
        // 生成一个样式  
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式  
        style.setFillForegroundColor(HSSFColor.WHITE.index);  
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置自动换行
        style.setWrapText(true);
        // 设置文字水平居中
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置文字垂直居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成一个字体  
        HSSFFont font = workbook.createFont();  
        font.setColor(HSSFColor.AUTOMATIC.index);
        font.setFontName("Arial");
        font.setFontHeightInPoints((short) 15);
        // 加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式  
        style.setFont(font);
        
        // 生成一个样式  
        HSSFCellStyle style_heji = workbook.createCellStyle();
        // 设置这些样式  
        style_heji.setFillForegroundColor(HSSFColor.WHITE.index);  
        style_heji.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        style_heji.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        style_heji.setBorderLeft(HSSFCellStyle.BORDER_NONE);  
        style_heji.setBorderRight(HSSFCellStyle.BORDER_NONE);  
        style_heji.setBorderTop(HSSFCellStyle.BORDER_NONE);
        // 设置自动换行
        style_heji.setWrapText(true);
        // 设置文字水平居中
        style_heji.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        // 设置文字垂直居中
        style_heji.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成一个字体  
        HSSFFont font_heji = workbook.createFont();  
        font_heji.setColor(HSSFColor.AUTOMATIC.index);
        font_heji.setFontName("Arial");
        font_heji.setFontHeightInPoints((short) 15);
        // 加粗
        font_heji.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式  
        style_heji.setFont(font_heji);
        
        workbook.setRepeatingRowsAndColumns(0, 0, headers.length - 1, 0, 2);
        
        sheet.setColumnWidth(0, 3000);
        sheet.setColumnWidth(1, 6000);
        sheet.setColumnWidth(2, 6000);
        sheet.setColumnWidth(3, 6000);
        sheet.setColumnWidth(4, 6000);
        sheet.setColumnWidth(5, 6000);
        // 生成一个样式  
        HSSFCellStyle style_data = workbook.createCellStyle();
        // 设置这些样式  
        style_data.setFillForegroundColor(HSSFColor.WHITE.index);  
        style_data.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        style_data.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        style_data.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        style_data.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        style_data.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置自动换行
        style_data.setWrapText(true);
        // 设置文字水平居中
        style_data.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置文字垂直居中
        style_data.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成一个字体  
        HSSFFont font_data = workbook.createFont();  
        font_data.setColor(HSSFColor.AUTOMATIC.index);
        font_data.setFontName("宋体");
        font_data.setFontHeightInPoints((short) 15);
        // 不加粗
        font_data.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        // 把字体应用到当前的样式  
        style_data.setFont(font_data);
        
        // 生成一个样式  
        HSSFCellStyle style_title = workbook.createCellStyle();
        // 设置这些样式  
        style_title.setFillForegroundColor(HSSFColor.WHITE.index);  
        style_title.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        style_title.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style_title.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成一个字体  
        HSSFFont font_title = workbook.createFont();  
        font_title.setColor(HSSFColor.AUTOMATIC.index);  
        font_title.setFontHeightInPoints((short) 20);
        font_title.setFontName("Arial");
        // 加粗
        font_title.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        // 把字体应用到当前的样式  
        style_title.setFont(font_title);
        
        // 产生title  
        HSSFRow row_title = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
        	HSSFCell cell = row_title.createCell(i);  
            cell.setCellStyle(style_title);
            cell.getCellStyle().setBorderBottom(HSSFCellStyle.BORDER_NONE);
            cell.getCellStyle().setBorderTop(HSSFCellStyle.BORDER_NONE);
            cell.getCellStyle().setBorderLeft(HSSFCellStyle.BORDER_NONE);
            cell.getCellStyle().setBorderRight(HSSFCellStyle.BORDER_NONE);
		}
        Cell cell_title = row_title.createCell(0);
        cell_title.setCellStyle(style_title);
        cell_title.setCellValue(title);
        // 合并单元格
        CellRangeAddress cra_title = new CellRangeAddress(0, 0, 0, headers.length - 1);
        //在sheet里增加合并单元格  
        sheet.addMergedRegion(cra_title);
        
        // 产生标题行
        HSSFRow row_header = sheet.createRow(1);
        for (short i = 0; i < headers.length; i++) { 
            HSSFCell cell = row_header.createCell(i);  
            cell.setCellStyle(style);  
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);  
            cell.setCellValue(text);
        }
        
        
        HSSFCellStyle cellStyle1 = workbook.createCellStyle();

        HSSFDataFormat format= workbook.createDataFormat();

        cellStyle1.setDataFormat(format.getFormat("yyyy-MM-dd hh:mm:ss"));
     // 设置这些样式  
        cellStyle1.setFillForegroundColor(HSSFColor.WHITE.index);  
        cellStyle1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        cellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        cellStyle1.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        cellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        cellStyle1.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置自动换行
        cellStyle1.setWrapText(true);
        // 设置文字水平居中
        cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置文字垂直居中
        cellStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cellStyle1.setFont(font_data);
        
        HSSFCellStyle cellStyle = workbook.createCellStyle();  
        // 设置这些样式  
        cellStyle.setFillForegroundColor(HSSFColor.WHITE.index);  
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置自动换行
        cellStyle.setWrapText(true);
        // 设置文字水平居中
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置文字垂直居中
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cellStyle.setFont(font_data);
        // 遍历集合数据，产生数据行  
        int index = 1;
        String chargingStationName = "";
        String lastStationNameString = "";
        int count = 1;
        int rowNo = 0;
        int startRow = 2;
        double totalKWH = 0;
        for(int i = 0; i < list.size(); i++) {
        	index++;
        	HSSFRow row = sheet.createRow(index);
        	HSSFCell cell_No = row.createCell(0);
        	cell_No.setCellStyle(style_data);
        	cell_No.setCellValue(index - 1);
        	
        	chargingStationName = list.get(i).getChargingStationName();
        	
        	HSSFCell cell_Cs = row.createCell(1);
        	cell_Cs.setCellStyle(style_data);
    		cell_Cs.setCellValue(chargingStationName);
    		
        	HSSFCell cell_Ln = row.createCell(2);
        	cell_Ln.setCellStyle(style_data);
        	cell_Ln.setCellValue(list.get(i).getLineName());
        	
        	HSSFCell cell_Tn = row.createCell(3);
        	cell_Tn.setCellStyle(style_data);
        	cell_Tn.setCellValue(list.get(i).getTeamName());
        	
        	HSSFCell cell_SK = row.createCell(4);
        	cell_SK.setCellStyle(style_data);
        	cell_SK.setCellValue(list.get(i).getSumChargingKWH());
        	totalKWH = DoubleUtil.add(totalKWH, list.get(i).getSumChargingKWH());

        }
        HSSFRow row = sheet.createRow(index + 1);
    	HSSFCell cell_No = row.createCell(0);
    	cell_No.setCellStyle(style_data);
    	cell_No.setCellValue(index);
    	
    	HSSFCell cell_Cs = row.createCell(1);
    	cell_Cs.setCellStyle(style_data);
		cell_Cs.setCellValue("总 计");
		
    	HSSFCell cell_Ln = row.createCell(2);
    	cell_Ln.setCellStyle(style_data);
    	cell_Ln.setCellValue("-");
    	
    	HSSFCell cell_Tn = row.createCell(3);
    	cell_Tn.setCellStyle(style_data);
    	cell_Tn.setCellValue("-");
    	
    	HSSFCell cell_SK = row.createCell(4);
    	cell_SK.setCellStyle(style_data);
    	cell_SK.setCellValue("-");
    	
        workbook.write(servletOutputStream);
        servletOutputStream.flush();
        servletOutputStream.close();
        
    }
}